﻿Imports System.Data.SqlClient
Imports System.Data
Public Class SinhVienDAO

    Public Function TimCMND(ByVal svDTO As SinhVienDTO) As DataTable
        Dim cnn As New SqlConnection
        Dim dataProvider As New DataProvider
        Dim da As SqlDataAdapter
        Dim strCon As String = "select *from SINHVIEN where CMND like '%" + svDTO.CMND + "%'"
        cnn = dataProvider.ConnectionData()
        da = New SqlDataAdapter(strCon, cnn)
        Dim data As New DataTable
        Try
            da.Fill(data)
        Catch ex As Exception

        End Try
        Return data
    End Function
    Public Function TimDienThoai(ByVal svDTO As SinhVienDTO) As DataTable
        Dim cnn As New SqlConnection
        Dim dataProvider As New DataProvider
        Dim da As SqlDataAdapter
        Dim strCon As String = "select *from SINHVIEN where DienThoai like '%" + svDTO.DienThoai + "%'"
        cnn = dataProvider.ConnectionData()
        da = New SqlDataAdapter(strCon, cnn)
        Dim data As New DataTable
        Try
            da.Fill(data)
        Catch ex As Exception

        End Try
        Return data
    End Function
    Public Function TimEmail(ByVal svDTO As SinhVienDTO) As DataTable
        Dim cnn As New SqlConnection
        Dim dataProvider As New DataProvider
        Dim da As SqlDataAdapter
        Dim strCon As String = "select *from SINHVIEN where Email like '%" + svDTO.Email + "%'"
        cnn = dataProvider.ConnectionData()
        da = New SqlDataAdapter(strCon, cnn)
        Dim data As New DataTable
        Try
            da.Fill(data)
        Catch ex As Exception

        End Try
        Return data
    End Function

    'Public Function TimSinhVien(ByVal svDTO As SinhVienDTO) As DataTable
    '    Dim cnn As New SqlConnection
    '    Dim dataProvider As New DataProvider
    '    Dim da As SqlDataAdapter
    '    Dim strCon As String = "select *from SINHVIEN where MaSinhVien like '%" + svDTO.MaSinhVien.ToString() + "%' or HoTen like '%" + svDTO.HoTen + "%' or CMND like '%" + svDTO.CMND + "%' or DienThoai like '%" + svDTO.DienThoai + "%' or Email like '%" + svDTO.Email + "%'"
    '    cnn = dataProvider.ConnectionData()
    '    da = New SqlDataAdapter(strCon, cnn)
    '    Dim data As New DataTable
    '    Try
    '        da.Fill(data)
    '    Catch ex As Exception

    '    End Try
    '    Return data
    'End Function
    Public Function TimMaSinhVien(ByVal svDTO As SinhVienDTO) As DataTable
        Dim cnn As New SqlConnection
        Dim dataProvider As New DataProvider
        Dim da As SqlDataAdapter
        Dim strCon As String = "select *from SINHVIEN where MaSinhVien like '%" + svDTO.MaSinhVien.ToString() + "%'"
        cnn = dataProvider.ConnectionData()
        da = New SqlDataAdapter(strCon, cnn)
        Dim data As New DataTable
        Try
            da.Fill(data)
        Catch ex As Exception

        End Try
        Return data
    End Function
    Public Function TimHoTen(ByVal svDTO As SinhVienDTO) As DataTable
        Dim cnn As New SqlConnection
        Dim dataProvider As New DataProvider
        Dim da As SqlDataAdapter
        Dim strCon As String = "select *from SINHVIEN where HoTen like '%" + svDTO.HoTen + "%'"
        cnn = dataProvider.ConnectionData()
        da = New SqlDataAdapter(strCon, cnn)
        Dim data As New DataTable
        Try
            da.Fill(data)
        Catch ex As Exception

        End Try
        Return data
    End Function
    Public Function CheckEmail(ByVal tk As SinhVienDTO) As Boolean
        Try
            Dim data As New DataProvider
            Dim cnn As New SqlConnection
            cnn = data.ConnectionData()
            Dim cmd As SqlCommand = New SqlCommand()
            cmd.Connection = cnn
            cmd.CommandText = "usp_KTTonTaiEmailSV"
            cmd.CommandType = CommandType.StoredProcedure

            Dim para As SqlParameter = cmd.Parameters.Add("@Email", SqlDbType.VarChar)
            para.Value = tk.Email

            Dim a As SqlParameter = New SqlParameter("@flag", SqlDbType.Int)
            a.Direction = ParameterDirection.Output
            cmd.Parameters.Add(a)

            cmd.ExecuteScalar()

            Dim flag As Integer = cmd.Parameters("@flag").Value
            If flag = 1 Then
                Return True
            End If
            Return False
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            Return True
        End Try
        Return False
    End Function
    Public Sub GetItemID(ByVal tenDN As String, ByRef sv As SinhVienDTO, ByRef tk As TaiKhoanDTO)
        Dim cnn As New SqlConnection
        Dim dataProvider As New DataProvider
        Dim da As SqlDataAdapter
        Dim strCon As String = "select tk.MaTaiKhoan,tk.TenTaiKhoan,tk.MatKhau,tk.MaLoai,tk.TrangThai,sv.MaSinhVien,sv.HoTen,sv.DienThoai,sv.CMND,sv.Email,sv.Truong FROM SINHVIEN sv, TAIKHOAN tk WHERE tk.MaTaiKhoan=sv.MaTaiKhoan and tk.TenTaiKhoan='" + tenDN + "'"
        cnn = dataProvider.ConnectionData()
        da = New SqlDataAdapter(strCon, cnn)
        Dim data As New DataTable
        da.Fill(data)
        Try
            sv.MaSinhVien = Convert.ToInt32(data.Rows(0)("MaSinhVien").ToString())
            sv.MaTaiKhoan = Convert.ToInt32(data.Rows(0)("MaTaiKhoan").ToString())
            sv.HoTen = data.Rows(0)("HoTen").ToString()
            sv.CMND = data.Rows(0)("CMND").ToString()
            sv.Email = data.Rows(0)("Email").ToString()
            sv.DienThoai = data.Rows(0)("DienThoai").ToString()
            sv.Truong = data.Rows(0)("Truong").ToString()

            tk.MaLoai = Convert.ToInt32(data.Rows(0)("MaLoai").ToString())
            tk.TenTaiKhoan = data.Rows(0)("TenTaiKhoan").ToString()
            tk.MatKhau = data.Rows(0)("MatKhau").ToString()
            tk.MaTaiKhoan = Convert.ToInt32(data.Rows(0)("MaTaiKhoan").ToString())
            tk.TrangThai = Convert.ToInt32(data.Rows(0)("TrangThai").ToString())
        Catch ex As Exception

        End Try

    End Sub
    Public Function GetItemMaSinhVien(ByVal masv As Integer) As SinhVienDTO
        Dim svDTO As New SinhVienDTO
        Dim cnn As New SqlConnection
        Dim dataProvider As New DataProvider
        Dim da As SqlDataAdapter
        Dim strCon As String = "select *from SINHVIEN where MaSinhVien=" + masv.ToString()
        cnn = dataProvider.ConnectionData()
        da = New SqlDataAdapter(strCon, cnn)
        Dim data As New DataTable
        da.Fill(data)
        Try
            svDTO.MaSinhVien = Convert.ToInt32(data.Rows(0)("MaSinhVien").ToString())
            svDTO.MaTaiKhoan = Convert.ToInt32(data.Rows(0)("MaTaiKhoan").ToString())
            svDTO.HoTen = data.Rows(0)("HoTen").ToString()
            svDTO.Email = data.Rows(0)("Email").ToString()
            svDTO.DienThoai = data.Rows(0)("DienThoai").ToString()
            svDTO.Truong = data.Rows(0)("Truong").ToString()
        Catch ex As Exception

        End Try

        Return svDTO
    End Function
    Public Function GetItemTenDN(ByVal tenDN As String) As SinhVienDTO
        Dim svDTO As New SinhVienDTO
        Dim cnn As New SqlConnection
        Dim dataProvider As New DataProvider
        Dim da As SqlDataAdapter
        Dim strCon As String = "select sv.MaSinhVien,sv.MaTaiKhoan,sv.HoTen,sv.CMND,sv.Email,sv.DienThoai,sv.Truong from SINHVIEN sv,TAIKHOAN tk where sv.MaTaiKhoan = tk.MaTaiKhoan and tk.TenTaiKhoan='" + tenDN + "'"
        cnn = dataProvider.ConnectionData()
        da = New SqlDataAdapter(strCon, cnn)
        Dim data As New DataTable
        da.Fill(data)
        Try
            svDTO.MaSinhVien = Convert.ToInt32(data.Rows(0)("MaSinhVien").ToString())
            svDTO.MaTaiKhoan = Convert.ToInt32(data.Rows(0)("MaTaiKhoan").ToString())
            svDTO.HoTen = data.Rows(0)("HoTen").ToString()
            svDTO.Email = data.Rows(0)("Email").ToString()
            svDTO.CMND = data.Rows(0)("CMND").ToString()
            svDTO.DienThoai = data.Rows(0)("DienThoai").ToString()
            svDTO.Truong = data.Rows(0)("Truong").ToString()
        Catch ex As Exception

        End Try

        Return svDTO
    End Function
    Public Function GetAll() As DataTable
        Dim cnn As New SqlConnection
        Dim dataProvider As New DataProvider
        Dim da As SqlDataAdapter
        Dim strCon As String = "select *from SINHVIEN"
        cnn = dataProvider.ConnectionData()
        da = New SqlDataAdapter(strCon, cnn)
        Dim data As New DataTable
        Try
            da.Fill(data)
        Catch ex As Exception

        End Try
        Return data
    End Function
    Public Function Insert(ByVal sv As SinhVienDTO) As Boolean
        Try
            Dim data As New DataProvider
            Dim cnn As New SqlConnection
            cnn = data.ConnectionData()
            Dim cmd As SqlCommand = New SqlCommand()
            cmd.Connection = cnn
            cmd.CommandText = "exec usp_InsertSINHVIEN @MaTaiKhoan,@HoTen,@DienThoai,@Email,@CMND,@Truong,@MaSinhVien"
            Dim para As SqlParameter = cmd.Parameters.Add("@MaTaiKhoan", SqlDbType.Int)
            para.Value = sv.MaTaiKhoan
            para = cmd.Parameters.Add("@HoTen", SqlDbType.NVarChar)
            para.Value = sv.HoTen
            para = cmd.Parameters.Add("@DienThoai", SqlDbType.VarChar)
            para.Value = sv.DienThoai
            para = cmd.Parameters.Add("@Email", SqlDbType.VarChar)
            para.Value = sv.Email
            para = cmd.Parameters.Add("@CMND", SqlDbType.VarChar)
            para.Value = sv.CMND
            para = cmd.Parameters.Add("@Truong", SqlDbType.NVarChar)
            para.Value = sv.Truong
            para = cmd.Parameters.Add("@MaSinhVien", SqlDbType.Int)
            para.Value = sv.MaSinhVien
            cmd.ExecuteNonQuery()
            Return True
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            Return False
        End Try
        Return False
    End Function
    Public Sub Delete(ByVal sv As SinhVienDTO)
        Try
            Dim data As New DataProvider
            Dim cnn As New SqlConnection
            cnn = data.ConnectionData()
            Dim cmd As SqlCommand = New SqlCommand()
            cmd.Connection = cnn
            cmd.CommandText = "exec usp_DeleteSINHVIEN @MaSinhVien"
            Dim para As SqlParameter = cmd.Parameters.Add("@MaSinhVien", SqlDbType.Int)
            para.Value = sv.MaSinhVien
            cmd.ExecuteNonQuery()
            MessageBox.Show("Sinh viên đã được xóa thành công", "Thông báo")
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub
    Public Sub Update(ByVal sv As SinhVienDTO)
        Try
            Dim data As New DataProvider
            Dim cnn As New SqlConnection
            cnn = data.ConnectionData()
            Dim cmd As SqlCommand = New SqlCommand()
            cmd.Connection = cnn
            cmd.CommandText = "exec usp_UpdateSINHVIEN @MaSinhVien,@HoTen,@DienThoai,@Email,@CMND,@Truong"
            Dim para As SqlParameter = cmd.Parameters.Add("@MaSinhVien", SqlDbType.Int)
            para.Value = sv.MaSinhVien
            para = cmd.Parameters.Add("@HoTen", SqlDbType.NVarChar)
            para.Value = sv.HoTen
            para = cmd.Parameters.Add("@DienThoai", SqlDbType.VarChar)
            para.Value = sv.DienThoai
            para = cmd.Parameters.Add("@Email", SqlDbType.VarChar)
            para.Value = sv.Email
            para = cmd.Parameters.Add("@CMND", SqlDbType.VarChar)
            para.Value = sv.CMND
            para = cmd.Parameters.Add("@Truong", SqlDbType.NVarChar)
            para.Value = sv.Truong
            cmd.ExecuteNonQuery()
            'MessageBox.Show("Thông tin đã được cập nhật thành công", "Thông báo")
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub
End Class
